package com.luoy.db;

import cn.hutool.json.JSONUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * @desc:
 * @author: luoy
 * @date: 2022-06-09 17:08
 */
@Slf4j
public class DBHelpInfo {


    private static final String driver = "com.mysql.cj.jdbc.Driver";

    private static final String pwd = "123456";

    private static final String user = "root";

    private static final String url = "jdbc:mysql://172.22.5.76:3306/xuanyuan_test?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&&useSSL=false";

    public static void main(String[] args) {

        String tableName = "c_auth_menu_bak";
        List<ColumnDetail> list = new ArrayList<>();
        ResultSet rsColumn = null;
        ResultSet rsPrim = null;
        ResultSet rsTable = null;
        Connection con = null;
        DatabaseMetaData dbmd;
        try {
            con = getConnections();
            dbmd = con.getMetaData();
	        /*
	         * 参数:catalog:目录名称，一般都为空.
			   参数：schema:数据库名，对于oracle来说就用户名
			   参数：tablename:表名称
			   参数：type :表的类型(TABLE | VIEW)
	         * */
            rsColumn = dbmd.getColumns(con.getCatalog(), con.getSchema(), tableName, "%");

            rsTable = dbmd.getTables(con.getCatalog(), con.getSchema(), tableName, new String[]{"TABLE"});
            String tableCnName = "";
            while (rsTable.next()) {
                tableCnName = rsTable.getString("REMARKS");       //表备注
         //表备注
            }
            while (rsColumn.next()) {
                ColumnDetail columnDetail = new ColumnDetail();
                columnDetail.setTableName(tableName);
                String column_name = rsColumn.getString("COLUMN_NAME");
                if (column_name.equals("sort_value")){
                    System.out.println("w");
                }
                columnDetail.setField(rsColumn.getString("COLUMN_NAME"));
                if (rsColumn.getString("REMARKS") != null) {
                    columnDetail.setComment(rsColumn.getString("REMARKS"));
                }
                columnDetail.setDataType(rsColumn.getString("TYPE_NAME"));
                columnDetail.setFieldSize(rsColumn.getString("COLUMN_SIZE"));
                  columnDetail.setScale(rsColumn.getInt("DECIMAL_DIGITS"));
                columnDetail.setTableCnName(tableCnName);
                columnDetail.setOrdinalPosition(rsColumn.getInt("ORDINAL_POSITION"));
                list.add(columnDetail);
            }

            try {
                rsPrim = dbmd.getPrimaryKeys(con.getCatalog(), con.getSchema(), tableName);
                while (rsPrim.next()) {
                    for (ColumnDetail columnDetail : list) {
                        if (columnDetail.getField().equalsIgnoreCase(rsPrim.getString("COLUMN_NAME"))) {
                            columnDetail.setPriKey(true);
                        }
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }

        } catch (Exception e) {
        } finally {
        }




        System.out.println(JSONUtil.toJsonStr(list));
    }

    public static void main2(String[] args) throws Exception{
        List<ColumnDetail> columnDetails = new ArrayList<>();
        String tableName = "c_auth_menu_bak";
        try (Connection con = getConnections();
             Statement ps = con.createStatement();
         ) {
            // 获取列数
            DatabaseMetaData dbmd = con.getMetaData();

            ResultSet   rsColumn = dbmd.getColumns(con.getCatalog(), con.getSchema(), tableName, "%");

            ResultSet   rsTable = dbmd.getTables(con.getCatalog(), con.getSchema(), tableName, new String[]{"TABLE"});

            ResultSetMetaData metaData = rsColumn.getMetaData();
            int columnCount = metaData.getColumnCount();
            // 遍历ResultSet中的每条数据
                ColumnDetail columnDetail = new ColumnDetail();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    String type = metaData.getColumnTypeName(i);
                    int precision = metaData.getPrecision(i);
                    int scale = metaData.getScale(i);
                    columnDetail.setScale(scale);
                    columnDetail.setField(columnName);
                    columnDetail.setFieldSize(String.valueOf(precision));
                    columnDetail.setScale(scale);
                    log.info("scale:{},,schema:{},column:{}", metaData.getScale(i), metaData.getSchemaName(i), metaData.getColumnName(i));
                    columnDetails.add(columnDetail);

                }
        }
        System.out.println(JSONUtil.toJsonStr(columnDetails));
    }


    public static void main11(String[] args) throws Exception{
        List<ColumnDetail> columnDetails = new ArrayList<>();
        try (Connection con = getConnections();
             Statement ps = con.createStatement();
             ResultSet rs = ps.executeQuery("select * from c_auth_menu_bak")) {
            // 获取列数
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            // 遍历ResultSet中的每条数据

                for (int i = 1; i <= columnCount; i++) {
                    ColumnDetail columnDetail = new ColumnDetail();
                    String columnName = metaData.getColumnLabel(i);
                    String type = metaData.getColumnTypeName(i);
                    int precision = metaData.getPrecision(i);
                    int scale = metaData.getScale(i);
                    columnDetail.setScale(scale);
                    columnDetail.setField(columnName);
                    columnDetail.setFieldSize(String.valueOf(precision));
                    columnDetail.setScale(scale);
                    log.info("scale:{},,schema:{},column:{}", metaData.getScale(i), metaData.getSchemaName(i), metaData.getColumnName(i));
                    columnDetails.add(columnDetail);
                }

        }
        System.out.println(JSONUtil.toJsonStr(columnDetails));
    }
    private static Connection getConnections() throws Exception {

        Connection connection = null;


        Class.forName("com.mysql.cj.jdbc.Driver");
        Properties props = new Properties();
        props.setProperty("user", user);
        if (StringUtils.isNotBlank(pwd)) {
            props.setProperty("password", pwd);
        }
        return DriverManager.getConnection(url, props);
    }

}
